﻿using System;

using System.Data;
using System.IO;
using System.Windows.Forms;
using ConfExpert.Forms.Base;
using Extensions;

namespace ConfExpert.Forms.Araclar
{
    public partial class ConsolidateSheets : XForm
    {
        public ConsolidateSheets()
        {
            InitializeComponent();
        }

        private void ConsolidateSheets_Load(object sender, EventArgs e)
        {
            ds.Clear();
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
                if (openFileDialog1.OpenFile() != null)
                {
                    DataTable dt = ReadExcel(openFileDialog1.FileName);
                    if (dt == null)
                        return;

                    gridView1.Init(dt);
                }
        }

        /*
        public DataTable OldReadExcel(string filename)
        {
            if (filename.EndsWith(".xls"))
            {
                ShowMessage("Lütfen dosyayı XLSX formatında kaydedin");
                return null;
            }

            bool hasHeader = false;
            bool ColumnsInserted = false;
            DataTable table = new DataTable("x");
            using (var excelFile = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = File.OpenRead(filename))
                {
                    excelFile.Load(stream);
                }

                foreach (ExcelWorksheet ws in excelFile.Workbook.Worksheets)
                    if (ws.Dimension != null)
                    {
                        //table.TableName = ws.Name;

                        if (!ColumnsInserted)
                        {
                            for (int i = 0; i < ws.Dimension.End.Column; i++)
                                table.Columns.Add(string.Format("Column {0}", i));

                            table.Columns.Add("SheetName");
                            ColumnsInserted = true;
                        }

                        var startRow = hasHeader ? 2 : 1;
                        for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                        {
                            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
                            var row = table.NewRow();
                            foreach (var cell in wsRow)
                                row[cell.Start.Column - 1] = cell.Text;

                            row[ws.Dimension.End.Column] = ws.Name;

                            table.Rows.Add(row);
                        }
                        //ds.Tables.Add(table);
                    }

            }
            return table;
        }
        */


        public DataTable ReadExcel(string filename)
        {
            SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(filename);
            System.Data.DataSet dataSet = workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);

            bool firstrow = false;
            foreach (DataTable d in dataSet.Tables)
            {
                d.Columns.Add("SheetName", typeof(string));

                foreach (DataRow r in d.Rows)
                    r["SheetName"] = d.TableName;

                if (!firstrow)
                {
                    d.Rows.RemoveAt(0);
                    firstrow = true;
                }
                else
                    dataSet.Tables[0].Merge(d);
            }

            return dataSet.Tables[0];

        }

    }
}
